﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using NUnit.Framework;
using PropertyExpression.Common;
using PropertyExpression.ControllableQuery.Test.Queries;
using PropertyExpression.ControllableQuery.Test.Queries.StringBuilding;
using PropertyExpression.ControllableQuery.Test.Queries.T4;
using PropertyExpression.ControllableQuery.TypedReader;
using ModelPublicationSearchQuery = PropertyExpression.ControllableQuery.Test.Queries.T4.ModelPublicationSearchQuery;

namespace PropertyExpression.ControllableQuery.Test
{
    [TestFixture]
    public class MainTest
    {
        //TODO: uncoment and delete
        //[Test]
        //public void PartLinkQuery()
        //{
        //    Console.WriteLine("Test1");
        //    WriteQueryResult(
        //        ExecuteReader(
        //            queryContext => new PartLinkQuery(
        //                                queryContext.Param(Guid.NewGuid()),
        //                                queryContext.Param(Guid.NewGuid()),
        //                                queryContext.Param(Guid.NewGuid()),
        //                                new WithoutInnerPartLinkSwitcher()
        //                                )
        //            )
        //        );
        //    Console.WriteLine("Test2");
        //    WriteQueryResult(
        //        ExecuteReader(
        //            queryContext => new PartLinkQuery(
        //                                queryContext.Param(Guid.NewGuid()),
        //                                queryContext.Param(Guid.NewGuid()),
        //                                queryContext.Param(Guid.NewGuid()),
        //                                new WithInnerPartLinkSwitcher(
        //                                    false,
        //                                    queryContext.Param("en"),
        //                                    queryContext.Param(Guid.NewGuid()))
        //                                )
        //            )
        //        );
        //    Console.WriteLine("Test3");
        //    WriteQueryResult(
        //        ExecuteReader(
        //            queryContext => new PartLinkQuery(
        //                                queryContext.Param(Guid.NewGuid()),
        //                                queryContext.Param(Guid.NewGuid()),
        //                                queryContext.Param(Guid.NewGuid()),
        //                                new WithInnerPartLinkSwitcher(
        //                                    true,
        //                                    queryContext.Param("en"),
        //                                    queryContext.Param(Guid.NewGuid()))
        //                                )
        //            )
        //        );
        //}

        //TODO: uncoment and delete
        //[Test]
        //public void ModelPublicationSearchQuery()
        //{
        //    Console.WriteLine("Test1");
        //    WriteQueryResult(
        //        ExecuteReader(
        //            queryContext => new ModelPublicationSearchQuery(
        //                                queryContext.Param("PublicationCode1").AsOptionalValue(),
        //                                OptionalValue.Nothing<IParam<string>>(),
        //                                OptionalValue.Nothing<IParam<string>>()
        //                                )
        //            )
        //        );
        //    Console.WriteLine("Test2");
        //    WriteQueryResult(
        //        ExecuteReader(
        //            queryContext => new ModelPublicationSearchQuery(
        //                                queryContext.Param("PublicationCode1").AsOptionalValue(),
        //                                queryContext.Param("ModelCode1").AsOptionalValue(),
        //                                queryContext.Param("ModelSequence1").AsOptionalValue()
        //                                )
        //            )
        //        );
        //    Console.WriteLine("Test3");
        //    WriteQueryResult(
        //        ExecuteReader(
        //            queryContext => new ModelPublicationSearchQuery(
        //                                OptionalValue.Nothing<IParam<string>>(),
        //                                OptionalValue.Nothing<IParam<string>>(),
        //                                OptionalValue.Nothing<IParam<string>>()
        //                                )
        //            )
        //        );
        //}

        //TODO: delete
        //[Test]
        //public void TableValuedParameterQuery()
        //{
        //    WriteQueryResult(
        //        ExecuteReader(
        //            queryContext =>
        //            new TableValuedParameterQuery(
        //                queryContext.Param(
        //                    new IPublicationCodeType[]
        //                        {
        //                            new PublicationCodeTypeImpl("PublicationCode1"),
        //                            new PublicationCodeTypeImpl("PublicationCode2")
        //                        }.AsTableValued()
        //                    )
        //                )
        //            )
        //        );
        //}

        [Test]
        public void StringBuildingModelPublicationSearchQuery()
        {
            Console.WriteLine("Test1");
            WriteQueryResult(
                ExecuteReader(
                    queryContext => new Queries.StringBuilding.ModelPublicationSearchQuery(
                                        queryContext.Param("PublicationCode1").AsOption(),
                                        Option.Nothing<IParam<string>>(),
                                        Option.Nothing<IParam<string>>()
                                        )
                    )
                );
            Console.WriteLine("Test2");
            WriteQueryResult(
                ExecuteReader(
                    queryContext => new Queries.StringBuilding.ModelPublicationSearchQuery(
                                        queryContext.Param("PublicationCode1").AsOption(),
                                        queryContext.Param("ModelCode1").AsOption(),
                                        queryContext.Param("ModelSequence1").AsOption()
                                        )
                    )
                );
            Console.WriteLine("Test3");
            WriteQueryResult(
                ExecuteReader(
                    queryContext => new Queries.StringBuilding.ModelPublicationSearchQuery(
                                        Option.Nothing<IParam<string>>(),
                                        Option.Nothing<IParam<string>>(),
                                        Option.Nothing<IParam<string>>()
                                        )
                    )
                );
        }

        [Test]
        public void StringBuildingModelPublicationQuery()
        {
            WriteQueryResult(
                ExecuteReader(
                    queryContext => new ModelPublicationQuery(
                                        queryContext.Param("ModelCode1"),
                                        queryContext.Param("ModelSequence1")
                                        )
                    )
                );
        }

        [Test]
        public void StringBuildingModelPublicationQuery2()
        {
            WriteQueryResult(
                ExecuteReader(
                    queryContext => new ModelPublicationQuery2(
                                        queryContext.Param("ModelCode1"),
                                        queryContext.Param("ModelSequence1")
                                        )
                    )
                );
        }

        [Test]
        public void PublicationTypeQueries()
        {
            var guid = Guid.NewGuid();
            ExecuteNonQuery(
                queryContext => new PublicationTypeInsertQuery(
                                    queryContext.Param(guid),
                                    queryContext.Param(1),
                                    queryContext.Param(new NVarChar<L4000>("Description1")),
                                    queryContext.Param(new Decimal<P18, S7>(123)),
                                    queryContext.Param(new Money(123))
                                    )
                );

            ExecuteNonQuery(
                queryContext => new PublicationTypeUpdateQuery(
                                    queryContext.Param(guid),
                                    queryContext.Param(Option.Nothing<NVarChar<L4000>>())
                                    )
                );

            const string @null = "NULL";
            Assert.AreEqual(
                @null,
                ExecuteReader(context => new PublicationTypeSelectQuery(context.Param(guid)))
                    .Select(reader => reader.Description().Process(_ => _, () => @null)).Single()
                );

            foreach (var dataReader in ExecuteReader(context => new PublicationTypeSelectQuery(context.Param(guid))))
            {
                Console.WriteLine(
                    "{0} {1} {2}",
                    dataReader.Description().Process(_ => _, () => @null),
                    dataReader.Decimal1(),
                    dataReader.Money1());
            }

            ExecuteNonQuery(
                queryContext => new PublicationTypeDeleteQuery(
                                    queryContext.Param(guid)
                                    )
                );
        }

        [Test]
        public void ModelPublicationQuery()
        {
            WriteQueryResult(
                ExecuteReader(
                    queryContext => Query.ModelPublicationQuery(
                        queryContext.Param("ModelCode1"),
                        queryContext.Param("ModelSequence1"))
                    )
                );

            WriteQueryResult(
                ExecuteReader(
                    queryContext => Query.ModelPublicationQuery(
                        queryContext.Param(() => "ModelCode1"),
                        queryContext.Param(() => "ModelSequence1"))
                    )
                );
        }

        [Test]
        public void PreparedSqlCommand()
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                using (var sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = connection;

                    var id = sqlCommand.CreateGuidParam();
                    var code = sqlCommand.CreateIntParam();
                    var description = sqlCommand.CreateNVarCharParam<L4000>();
                    var decimal1 = sqlCommand.CreateDecimalParam<P18, S7>();
                    var money1 = sqlCommand.CreateMoneyParam();
                    sqlCommand.CommandText = new PublicationTypeInsertQuery(id, code, description, decimal1, money1).TransformText();
                    sqlCommand.Prepare();

                    for (var i = 0; i < 100; i++)
                    {
                        id.Value = Guid.NewGuid();
                        code.Value = i;
                        description.Value = new NVarChar<L4000>("Description" + i);
                        decimal1.Value = new Decimal<P18, S7>(i);
                        money1.Value = new Money(i);
                        sqlCommand.ExecuteNonQuery();                        
                    }
                }
            }
        }

        private static void WriteQueryResult(IEnumerable<IDataReader<IPartLinkInfo>> queryEnumerable)
        {
            foreach (var dataReader in queryEnumerable)
            {
                Console.WriteLine(
                    "PartNumber = '{0}'",
                    dataReader.PartNumber().Process(value => value, () => "NULL")
                    );
            }
        }

        private static void WriteQueryResult(IEnumerable<IDataReader<IModelPublicationInfo>> queryEnumerable)
        {
            foreach (var dataReader in queryEnumerable)
            {
                Console.WriteLine(
                    "PublicationId = '{0}', ModelCode = '{1}', ModelSequence = '{2}'",
                    dataReader.PublicationId(),
                    dataReader.ModelCode(),
                    dataReader.ModelSequence()
                    );
            }
        }

        private static void WriteQueryResult(IEnumerable<IDataReader<IPublicationInfo>> queryEnumerable)
        {
            foreach (var dataReader in queryEnumerable)
            {
                Console.WriteLine(
                    "PublicationId = '{0}'",
                    dataReader.PublicationId()
                    );
            }
        }

        public static IEnumerable<IDataReader<T>> ExecuteReader<T>(Func<SqlCommand, IQuery<T>> queryFunc)
        {
            return WithSqlCommand(queryFunc, ExecuteReader);
        }

        public static int ExecuteNonQuery(Func<SqlCommand, IQuery> queryFunc)
        {
            return WithSqlCommand(queryFunc, (query, sqlCommand) => new[] {sqlCommand.ExecuteNonQuery()}).Single();
        }

        private static IEnumerable<IDataReader<T>> ExecuteReader<T>(IQuery<T> query, SqlCommand sqlCommand)
        {
            using (var sqlDataReader = sqlCommand.ExecuteReader())
            {
                while (sqlDataReader.Read())
                {
                    yield return query.GetReader(sqlDataReader);
                }
            }
        }

        private static IEnumerable<TResult> WithSqlCommand<TQuery, TResult>(
            Func<SqlCommand, TQuery> queryFunc, Func<TQuery, SqlCommand, IEnumerable<TResult>> func) where TQuery : IQuery
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();

                using (var sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = connection;

                    var query = queryFunc(sqlCommand);

                    sqlCommand.CommandText = query.TransformText();

                    foreach (var result in func(query, sqlCommand)) yield return result;
                }
            }
        }

        public static string ConnectionString
        {
            get { return @"Data Source=(local);Initial Catalog=ControllableQuery2;Integrated Security=True"; }
        }

        private class PublicationCodeTypeImpl : IPublicationCodeType
        {
            private readonly string publicationCode;

            public PublicationCodeTypeImpl(string publicationCode)
            {
                this.publicationCode = publicationCode;
            }

            public string PublicationCode
            {
                get { return publicationCode; }
            }
        }
    }
}